const express = require('express');
const multer = require('multer');
const ExcelJS = require('exceljs');
const router = express.Router();
const {
  getAllCadres,
  getCadreById,
  createCadre,
  updateCadre,
  deleteCadre,
  getTeachingDirections
} = require('../controllers/cadreController');

const Cadre = require('../models/Cadre');
const Unit = require('../models/Unit');
const University = require('../models/University');

// 配置multer用于文件上传
const storage = multer.memoryStorage();
const upload = multer({
  storage: storage,
  limits: {
    fileSize: 10 * 1024 * 1024, // 10MB
  },
  fileFilter: (req, file, cb) => {
    if (file.mimetype === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ||
        file.mimetype === 'application/vnd.ms-excel') {
      cb(null, true);
    } else {
      cb(new Error('只支持Excel文件格式(.xlsx, .xls)'), false);
    }
  }
});

// POST /api/cadres/preview - 预览Excel数据（不保存）
router.post('/preview', upload.single('file'), async (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ error: '请上传Excel文件' });
    }

    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.load(req.file.buffer);

    const worksheet = workbook.getWorksheet(1);
    if (!worksheet) {
      return res.status(400).json({ error: 'Excel文件格式错误' });
    }

    // 获取表头
    const headerRow = worksheet.getRow(1);
    const headers = [];
    headerRow.eachCell((cell, colNumber) => {
      headers[colNumber] = cell.value;
    });

    // 字段映射
    const fieldMappings = {
      '姓名': 'name',
      '性别': 'gender',
      '出生年月': 'birthDate',
      '级别': 'level',
      '干部类型': 'cadreType',
      '所属单位': 'unit',
      '最高学历': 'highestEducation',
      '最高学位': 'highestDegree',
      '拟授课方向': 'proposedTeachingDirections',
      '意向高校1': 'intendedUniversity1',
      '意向高校2': 'intendedUniversity2',
      '意向高校3': 'intendedUniversity3',
      '人事变动': 'personnelChanges',
      '备注': 'remarks'
    };

    // 验证表头
    const requiredHeaders = ['姓名', '性别', '出生年月', '所属单位', '拟授课方向', '意向高校1'];
    const missingHeaders = requiredHeaders.filter(header => !headers.includes(header));
    if (missingHeaders.length > 0) {
      return res.status(400).json({
        error: `Excel表头缺少必要字段: ${missingHeaders.join(', ')}`
      });
    }

    // 获取单位和高校数据
    const units = await Unit.find({}, 'name').lean();
    const universities = await University.find({}, 'schoolName').lean();

    const unitMap = new Map(units.map(u => [u.name, u._id]));
    const universityMap = new Map(universities.map(u => [u.schoolName, u._id]));

    const validData = [];
    const invalidData = [];
    let total = 0;

    // 处理数据行
    for (let rowNumber = 2; rowNumber <= worksheet.rowCount; rowNumber++) {
      const row = worksheet.getRow(rowNumber);

      // 检查是否为空行
      let hasData = false;
      row.eachCell((cell) => {
        if (cell.value) hasData = true;
      });

      if (!hasData) continue;

      total++;
      const rowData = {};

      try {
        // 解析每个单元格
        headers.forEach((header, colIndex) => {
          if (header && fieldMappings[header]) {
            const cell = row.getCell(colIndex);
            rowData[fieldMappings[header]] = cell.value;
          }
        });

        // 构建干部数据
        const cadreData = {
          name: rowData.name?.toString().trim(),
          gender: rowData.gender,
          birthDate: rowData.birthDate ? new Date(rowData.birthDate).toISOString().split('T')[0] : null,
          level: rowData.level,
          cadreType: rowData.cadreType,
          unit: rowData.unit?.toString().trim(),
          highestEducation: rowData.highestEducation,
          highestDegree: rowData.highestDegree,
          personnelChanges: rowData.personnelChanges?.toString().trim(),
          remarks: rowData.remarks?.toString().trim()
        };

        // 处理所属单位
        if (rowData.unit) {
          const unitName = rowData.unit.toString().trim();
          const unitId = unitMap.get(unitName);
          if (unitId) {
            cadreData.unitId = unitId;
          } else {
            throw new Error(`单位"${unitName}"不存在`);
          }
        }

        // 处理拟授课方向
        if (rowData.proposedTeachingDirections) {
          const directions = rowData.proposedTeachingDirections.toString().split(/[、，,;；]/).map(d => d.trim()).filter(d => d);

          // 预定义的拟授课方向选项
          const validDirections = [
            '坚持党的全面领导',
            '推动高质量发展',
            '全过程人民民主',
            '全面依法治国',
            '社会主义核心价值观',
            '保障和改善民生',
            '人与自然和谐共生',
            '国家安全',
            '一国两制',
            '构建人类命运共同体',
            '全面从严治党'
          ];
          // 只保留有效的方向，最多2个
          const filteredDirections = directions.filter(d => validDirections.includes(d)).slice(0, 2);

          // 如果没有有效方向，使用默认值
          if (filteredDirections.length === 0) {
            cadreData.proposedTeachingDirections = ['坚持党的全面领导'];
          } else {
            cadreData.proposedTeachingDirections = filteredDirections;
          }
        } else {
          cadreData.proposedTeachingDirections = ['坚持党的全面领导'];
        }

        // 处理意向高校
        ['intendedUniversity1', 'intendedUniversity2', 'intendedUniversity3'].forEach(field => {
          if (rowData[field]) {
            const universityName = rowData[field].toString().trim();
            const universityId = universityMap.get(universityName);
            if (universityId) {
              cadreData[field] = universityId;
              cadreData[field + 'Name'] = universityName; // 保存名称用于显示
            } else {
              throw new Error(`高校"${universityName}"不存在`);
            }
          }
        });

        // 验证必填字段
        if (!cadreData.name || !cadreData.gender || !cadreData.birthDate ||
            !cadreData.unitId || !cadreData.proposedTeachingDirections?.length ||
            !cadreData.intendedUniversity1) {
          throw new Error('缺少必填字段');
        }

        validData.push(cadreData);

      } catch (error) {
        invalidData.push({
          row: rowNumber,
          name: rowData.name || '未知',
          error: error.message
        });
      }
    }

    res.json({
      message: '解析完成',
      data: {
        total,
        valid: validData,
        invalid: invalidData
      }
    });

  } catch (error) {
    console.error('解析Excel文件失败:', error);
    res.status(500).json({ error: '解析失败', details: error.message });
  }
});

// GET /api/cadres - 获取所有干部
router.get('/', getAllCadres);

// GET /api/cadres/teaching-directions - 获取拟授课方向选项
router.get('/teaching-directions', getTeachingDirections);

// POST /api/cadres/import - 批量导入干部（必须在 /:id 之前）
router.post('/import', upload.single('file'), async (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ error: '请上传Excel文件' });
    }

    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.load(req.file.buffer);

    const worksheet = workbook.getWorksheet(1);
    if (!worksheet) {
      return res.status(400).json({ error: 'Excel文件格式错误' });
    }

    // 获取表头
    const headerRow = worksheet.getRow(1);
    const headers = [];
    headerRow.eachCell((cell, colNumber) => {
      headers[colNumber] = cell.value;
    });

    // 字段映射
    const fieldMappings = {
      '姓名': 'name',
      '性别': 'gender',
      '出生年月': 'birthDate',
      '级别': 'level',
      '干部类型': 'cadreType',
      '所属单位': 'unit',
      '最高学历': 'highestEducation',
      '最高学位': 'highestDegree',
      '拟授课方向': 'proposedTeachingDirections',
      '意向高校1': 'intendedUniversity1',
      '意向高校2': 'intendedUniversity2',
      '意向高校3': 'intendedUniversity3',
      '人事变动': 'personnelChanges',
      '备注': 'remarks'
    };

    // 验证表头
    const requiredHeaders = ['姓名', '性别', '出生年月', '所属单位', '拟授课方向', '意向高校1'];
    const missingHeaders = requiredHeaders.filter(header => !headers.includes(header));
    if (missingHeaders.length > 0) {
      return res.status(400).json({
        error: `Excel表头缺少必要字段: ${missingHeaders.join(', ')}`
      });
    }

    // 获取单位和高校数据
    const units = await Unit.find({}, 'name').lean();
    const universities = await University.find({}, 'schoolName').lean();

    const unitMap = new Map(units.map(u => [u.name, u._id]));
    const universityMap = new Map(universities.map(u => [u.schoolName, u._id]));

    const results = {
      total: 0,
      success: 0,
      failed: 0,
      errors: []
    };

    // 处理数据行
    for (let rowNumber = 2; rowNumber <= worksheet.rowCount; rowNumber++) {
      const row = worksheet.getRow(rowNumber);

      // 检查是否为空行
      let hasData = false;
      row.eachCell((cell) => {
        if (cell.value) hasData = true;
      });

      if (!hasData) continue;

      results.total++;
      const rowData = {};

      try {
        // 解析每个单元格
        headers.forEach((header, colIndex) => {
          if (header && fieldMappings[header]) {
            const cell = row.getCell(colIndex);
            rowData[fieldMappings[header]] = cell.value;
          }
        });

        // 构建干部数据
        const cadreData = {
          name: rowData.name?.toString().trim(),
          gender: rowData.gender,
          birthDate: rowData.birthDate ? new Date(rowData.birthDate) : null,
          level: rowData.level,
          cadreType: rowData.cadreType,
          highestEducation: rowData.highestEducation,
          highestDegree: rowData.highestDegree,
          personnelChanges: rowData.personnelChanges?.toString().trim(),
          remarks: rowData.remarks?.toString().trim()
        };

        // 处理所属单位
        if (rowData.unit) {
          const unitName = rowData.unit.toString().trim();
          const unitId = unitMap.get(unitName);
          if (unitId) {
            cadreData.unitId = unitId;
          } else {
            throw new Error(`单位"${unitName}"不存在`);
          }
        }

        // 处理拟授课方向
        if (rowData.proposedTeachingDirections) {
          const directions = rowData.proposedTeachingDirections.toString().split(/[、，,;；]/).map(d => d.trim()).filter(d => d);

          // 预定义的拟授课方向选项
          const validDirections = [
            '坚持党的全面领导',
            '推动高质量发展',
            '全过程人民民主',
            '全面依法治国',
            '社会主义核心价值观',
            '保障和改善民生',
            '人与自然和谐共生',
            '国家安全',
            '一国两制',
            '构建人类命运共同体',
            '全面从严治党'
          ];
          // 只保留有效的方向，最多2个
          const filteredDirections = directions.filter(d => validDirections.includes(d)).slice(0, 2);

          // 如果没有有效方向，使用默认值
          if (filteredDirections.length === 0) {
            cadreData.proposedTeachingDirections = ['坚持党的全面领导'];
          } else {
            cadreData.proposedTeachingDirections = filteredDirections;
          }
        } else {
          cadreData.proposedTeachingDirections = ['坚持党的全面领导'];
        }

        // 处理意向高校
        ['intendedUniversity1', 'intendedUniversity2', 'intendedUniversity3'].forEach(field => {
          if (rowData[field]) {
            const universityName = rowData[field].toString().trim();
            const universityId = universityMap.get(universityName);
            if (universityId) {
              cadreData[field] = universityId;
            } else {
              throw new Error(`高校"${universityName}"不存在`);
            }
          }
        });

        // 验证必填字段
        if (!cadreData.name || !cadreData.gender || !cadreData.birthDate ||
            !cadreData.unitId || !cadreData.proposedTeachingDirections?.length ||
            !cadreData.intendedUniversity1) {
          throw new Error('缺少必填字段');
        }

        // 确保拟授课方向不为空数组
        if (cadreData.proposedTeachingDirections.length === 0) {
          cadreData.proposedTeachingDirections = ['坚持党的全面领导']; // 默认值
        }

        // 创建干部记录
        const newCadre = new Cadre(cadreData);
        await newCadre.save();

        results.success++;

      } catch (error) {
        results.failed++;
        results.errors.push({
          row: rowNumber,
          name: rowData.name || '未知',
          error: error.message
        });
      }
    }

    res.json({
      message: '导入完成',
      data: results
    });

  } catch (error) {
    console.error('导入干部信息失败:', error);
    res.status(500).json({ error: '导入失败', details: error.message });
  }
});

// POST /api/cadres/import-confirmed - 确认导入预览的数据
router.post('/import-confirmed', async (req, res) => {
  try {
    const { data } = req.body;

    if (!data || !Array.isArray(data) || data.length === 0) {
      return res.status(400).json({ error: '没有有效数据可以导入' });
    }

    const results = {
      total: data.length,
      success: 0,
      failed: 0,
      errors: []
    };

    // 批量创建干部记录
    for (let i = 0; i < data.length; i++) {
      const cadreData = data[i];
      try {
        // 移除显示用的字段
        const cleanData = { ...cadreData };
        delete cleanData.intendedUniversity1Name;
        delete cleanData.intendedUniversity2Name;
        delete cleanData.intendedUniversity3Name;
        delete cleanData.unit; // 移除单位名称，只保留unitId

        const newCadre = new Cadre(cleanData);
        await newCadre.save();
        results.success++;
      } catch (error) {
        results.failed++;
        results.errors.push({
          row: i + 1,
          name: cadreData.name || '未知',
          error: error.message
        });
      }
    }

    res.json({
      message: '导入完成',
      data: results
    });

  } catch (error) {
    console.error('确认导入失败:', error);
    res.status(500).json({ error: '导入失败', details: error.message });
  }
});

// GET /api/cadres/:id - 根据ID获取干部
router.get('/:id', getCadreById);

// POST /api/cadres - 创建干部
router.post('/', createCadre);

// PUT /api/cadres/:id - 更新干部
router.put('/:id', updateCadre);

// DELETE /api/cadres/:id - 删除干部
router.delete('/:id', deleteCadre);

module.exports = router;
